package com.hst.capacity.infra.persistence.jpa.marketing.coupon;


import com.hst.capacity.domain.model.entity.marketing.coupon.CouponRuleInfoEntity;
import com.hst.capacity.domain.model.request.marketing.MarketSearchParam;
import com.hst.capacity.domain.model.response.driver.DriverBandLineVO;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.time.LocalDateTime;
import java.util.List;

public interface CouponRuleInfoRepository extends JpaRepository<CouponRuleInfoEntity, String> {

    @Query("select t from CouponRuleInfoEntity t where t.idFranchisee = :idFranchisee")
    List<CouponRuleInfoEntity> findByIdFranchisee(String idFranchisee);

    /**
     * 得当前获得优惠券规则
     * @param idPromotion
     * @return
     */
    @Query("select t " +
            "from CouponRuleInfoEntity t " +
            "inner join PromotionRelationEntity p on p.idCouponRule = t.idCouponRule " +
            "where t.enabled =1 " +
            "and p.idPromotion=:idPromotion")
    List<CouponRuleInfoEntity> findByIdPromotion(@Param("idPromotion") String idPromotion);

    /**
     * 根据加盟商ID与活动ID获得未绑定优惠券规则列表
     * @param idPromotion
     * @return
     */
    @Query("select t " +
            "from CouponRuleInfoEntity t " +
            "inner join PromotionEntity p on p.idFranchisee = t.idFranchisee " +
            "left join PromotionRelationEntity pr on pr.idCouponRule = t.idCouponRule and pr.idPromotion = p.idPromotion " +
            "where t.enabled =1 " +
            "and p.idFranchisee=:idFranchisee "+
            "and p.idPromotion=:idPromotion "+
            "and pr.idPromotion is null " )
    List<CouponRuleInfoEntity> findBandCouponRuleList(@Param("idFranchisee") String idFranchisee,@Param("idPromotion") String idPromotion);

    /**
     * 根据加盟商ID与活动ID获得已绑定优惠券规则列表
     * @param idPromotion
     * @return
     */
    @Query("select t " +
            "from CouponRuleInfoEntity t " +
            "inner join PromotionEntity p on p.idFranchisee = t.idFranchisee " +
            "left join PromotionRelationEntity pr on pr.idCouponRule = t.idCouponRule and pr.idPromotion = p.idPromotion " +
            "where t.enabled =1 " +
            "and p.idFranchisee=:idFranchisee "+
            "and p.idPromotion=:idPromotion "+
            "and pr.idPromotion is not null " )
    List<CouponRuleInfoEntity> findUnBandCouponRuleList(@Param("idFranchisee") String idFranchisee,@Param("idPromotion") String idPromotion);


//    /**
//     * todo 条件限定
//     * @param idFranchisee
//     * @return
//     */
//    @Query("select t from CouponRuleInfoEntity t where t.idFranchisee=:idFranchisee and t.enabled =1 and :today between t.startTime and t.endTime")
//    List<CouponRuleInfoEntity> findByIdFranchisee(@Param("idFranchisee") String idFranchisee, @Param("today") LocalDateTime today);

    /**
     * todo 促销活动条件限定
     * @param idPromotion
     * @return
     */
    @Query("select t " +
            "from CouponRuleInfoEntity t " +
            "inner join PromotionRelationEntity p on p.idCouponRule = t.idCouponRule " +
            "where t.enabled =1 and :today between t.startTime and t.endTime " +
            "and p.idPromotion=:idPromotion")
    List<CouponRuleInfoEntity> findByIdPromotion(@Param("idPromotion") String idPromotion, @Param("today") LocalDateTime today);

    /**
     * todo 促销活动条件限定
     * @param idFranchisee
     * @return
     */
    @Query("select t " +
            "from PromotionEntity p " +
            "inner join PromotionRelationEntity r on p.idPromotion = r.idPromotion " +
            "inner join CouponRuleInfoEntity t on r.idCouponRule = t.idCouponRule " +
            "where p.idFranchisee=:idFranchisee " +
            "and p.enabled =1 and :today between p.effectiveTime and p.expiredTime " +
            "and t.enabled =1 and :today between t.startTime and t.endTime " )
    List<CouponRuleInfoEntity> findByIdFranchisee(@Param("idFranchisee") String idFranchisee, @Param("today") LocalDateTime today);

    /**
     * todo 促销活动条件限定
     * @param regionCode
     * @return
     */
    @Query("select t " +
            "from PromotionEntity p " +
            "inner join PromotionRelationEntity r on p.idPromotion = r.idPromotion " +
            "inner join CouponRuleInfoEntity t on r.idCouponRule = t.idCouponRule " +
            "where p.regionCode=:regionCode " +
            "and p.enabled =1 and :today between p.effectiveTime and p.expiredTime " +
            "and t.enabled =1 and :today between t.startTime and t.endTime " )
    List<CouponRuleInfoEntity> findByRegionCode(@Param("regionCode") String regionCode, @Param("today") LocalDateTime today);


    /**
     * 获得当前加盟商的活动
     * @param idFranchisee
     * @return
     */
    @Query("select t from CouponRuleInfoEntity t " +
            "where " +
            "t.idFranchisee=:idFranchisee " +
            "and t.enabled =:enabled " +
            "ORDER BY t.createTime DESC" )
    List<CouponRuleInfoEntity> findByIdFranchisee(@Param("idFranchisee") String idFranchisee,
                                                  @Param("enabled") Integer enabled);
    /**
     * 获得当前加盟商的活动
     * @param idFranchisee
     * @return
     */
    @Query("select t from CouponRuleInfoEntity t " +
            "where " +
            "t.idFranchisee=:idFranchisee " +
            "and t.startTime >= :startDate and t.startTime <=:endDate " +
            "and t.enabled =:enabled " +
            "ORDER BY t.createTime DESC" )
    List<CouponRuleInfoEntity> findByIdFranchisee(@Param("idFranchisee") String idFranchisee,
                                                  @Param("startDate") LocalDateTime startDate,
                                                  @Param("endDate") LocalDateTime endDate,
                                                  @Param("enabled") Integer enabled);

}